﻿using Microsoft.Office.Interop.Excel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ExcelAutomationRunner
{
    public class ColumnReorderer : ExcelApplicationBase
    {

        private string _existingPath;
        private List<string> _columnNamesInNewOrder;

        public ColumnReorderer( int? maxMillisecondsToReleaseExcel = null ): base( maxMillisecondsToReleaseExcel )
        {
        }

        /// <summary>
        /// Works like a champ with huge files.
        /// It doesn't chew up much memory at all when copy/pasting gigantic columns of data (some seriously magical low-level memory management stuff going on in Excel).
        /// </summary>
        /// <param name="existingPath"></param>
        /// <param name="columnNamesInNewOrder"></param>
        /// <returns>success, resulting file path on success; error message on failure</returns>
        public Tuple<bool, string> Reorder( string existingPath, List<string> columnNamesInNewOrder )
        {
            if ( File.Exists(existingPath) )
            {
                _existingPath = existingPath;
                _columnNamesInNewOrder = columnNamesInNewOrder;
                return ProcessApplication();  // this calls Process below.
            }
            else
            {
                return new Tuple<bool, string>( false, "File does not exist: " + existingPath );
            }
        }

        protected override Tuple<bool, string> Process( Application excel )
        {
            _newPath = GetLocalFileName();
            return ReorderWith( excel );
        }

        private Tuple<bool, string> ReorderWith( Application excel )
        {
            var workbook = excel.Workbooks.Open( _existingPath );
            Worksheet existingWorksheet = excel.Workbooks[1].Worksheets[1];
            if ( ColumnsNeedReordering( existingWorksheet ) )
            {
                CreateNewWorkbook( excel, existingWorksheet );
                //_newWorkbook.SaveAs( _newPath );  // see comment in ProcessColumns() below
                _newWorkbook.Save();
                _newWorkbook.Close();
                workbook.Close();
                ReleaseComObject( workbook );
                return new Tuple<bool, string>( true, _newPath );
            }
            else
            {
                workbook.Close();
                ReleaseComObject( workbook );
                return new Tuple<bool, string>( true, _existingPath );
            }
        }

        private void CreateNewWorkbook( Application excel, Worksheet existingWorksheet )
        {
            _newWorkbook = excel.Workbooks.Add();
            _newWorkbook.SaveAs( _newPath );
            Worksheet newWorksheet = _newWorkbook.Worksheets[1];
            ProcessColumns( existingWorksheet, newWorksheet );
        }

        private void ProcessColumns( Worksheet existingWorksheet, Worksheet newWorksheet )
        {
            var newWorksheetColumnIndex = 0;
            _columnNamesInNewOrder.ForEach( existingColumnHead =>
            {
                var existingColumnIndex = ColumnIndexWithHead( existingColumnHead, existingWorksheet );
                if ( existingColumnIndex > 0 )
                {
                    var column = GetUsedColumnRange( existingWorksheet, existingColumnIndex );
                    if ( column != null )
                    {
                        column.Copy( newWorksheet.Cells[1, ++newWorksheetColumnIndex] );  // copy is really paste?
                        ReleaseComObject( column );
                        /// Seems logical that I should save on each column to keep memory usage down, but tests on large file
                        /// showed no spike in memory usage
                        //_newWorkbook.Save();
                    }
                }
            } );
        }

        private bool ColumnsNeedReordering( Worksheet existingWorksheet )
        {
            for ( int i = 0; i < _columnNamesInNewOrder.Count; i++ )
            {
                var existingColumnIndex = ColumnIndexWithHead( _columnNamesInNewOrder[i], existingWorksheet );
                if ( existingColumnIndex != i + 1 )  // columns 1 based
                {
                    return true;
                }
            }
            return false;
        }

        private static string GetLocalFileName()
        {
            var result = Path.GetTempFileName();
            result = Path.ChangeExtension( result, Constants.EXCEL_EXTENSION );
            return result;
        }
            }
}
